import cv2
import xlwt
import tkinter
import smtplib
import requests
import pandas as pd
from tkinter import *
from lxml import etree
import seaborn as sns
import tkinter.messagebox
import tkinter.simpledialog
import matplotlib.pyplot as plt
from wordcloud import WordCloud
from tkinter.messagebox import *
from xlrd import open_workbook
from xlutils.copy import copy
import mysql.connector as sql
from tkinter.ttk import Treeview, Progressbar
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication
#***********************************词云设置*******************************************************
font = r'C:\Windows\Fonts\simfang.ttf'
plt.rcParams['font.sans-serif'] = ["SimHei"]
plt.rcParams["axes.unicode_minus"] = False
#***********************************连接数据库*****************************************************
user="root"
password="123456"
host="localhost"
port="3306"
use_unicode="True"
try:
    conn = sql.connect(user=user,password=password,host=host,port=port,use_unicode=use_unicode)
except BaseException as e:
    print(e)
    print("sql连接失败")
    print(e.__traceback__.tb_lineno)
c = conn.cursor()
c.execute('use 大作业')#选择当下的数据库
#**********************************网路设置******************************************************
headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.45 Safari/537.36'}
url_q="https://www.lagou.com/wn/jobs?px=new&gj=%E5%9C%A8%E6%A0%A1%2F%E5%BA%94%E5%B1%8A&pn="
page=0
url_h="&kd="

#***********************************邮箱设置*****************************************************
host = 'smtp.qq.com'  # SMTP服务器
sender = '1019167666@qq.com'  # 邮箱账号
password = 'frrdzkvxcoklbcii'  # 授权码

#记录操作
def yonghu(ac,cz):
    try:
        sql = "insert into 用户操作表  values (\'" + str(ac) + "\',\'" + str(cz) + "\');"
        print(sql)
        c.execute(sql)
        conn.commit()
    except:
        print("跳过")
def gly(ac,cz):
    try:
        sql = "insert into 管理员操作表  values (\'" + str(ac) + "\',\'" + str(cz) + "\');"
        print(sql)
        c.execute(sql)
        conn.commit()
    except:
        print("跳过")


#主界面
def run():
    #登录按钮
    def denglu():
        ac = account.get()#获取账号
        ps = password.get()#获取密码
        if (ac == "" or ps == ""):
            showinfo("用户登录", "请完整填写信息！！")
        else:
            #先看看是不是管理员
            com = "select 管理员密码 from 管理员表 where 管理员账号=" + str(ac) + ";"
            try:

                zpslist = pd.read_sql(com, conn)
                print(zpslist)
                zps = zpslist.loc[0, '管理员密码']
                if ps==zps:
                    account.delete(0, 'end')  # 清空文本框的内容
                    password.delete(0, 'end')  # 清空文本框的内容
                    showinfo("管理员登录", "登录成功！即将进入管理员界面....")
                    index.destroy()
                    guanli(ac)

            except:
                print("不是管理员")
            com = "select 用户密码,判定值 from 用户表 where 用户账号=" + str(ac) + ";"
            try:
                zpslist = pd.read_sql(com, conn)
                print(zpslist)
                zps = zpslist.loc[0, '用户密码']
                pdz = zpslist.loc[0, '判定值']
                print(pdz)
            except BaseException as e:
                print("没有找到账号")
                zps = ""
            if ps == zps:
                account.delete(0, 'end')  # 清空文本框的内容
                password.delete(0, 'end')  # 清空文本框的内容
                if pdz == '1':  # 判定唯1
                    showinfo("用户登录", "登录成功！即将进入菜单界面....")
                    index.destroy()
                    chaxun(ac)

                elif pdz == '2':  # 判定唯2
                    showinfo("用户登录", "登录失败，该账号还在审核中")

                elif pdz == '3':  # 判定唯3
                    showinfo("用户登录", "登录失败，审核失败")
                    try:
                        com = "select 失败原因 from 注册失败表 where 用户账号=" + str(ac) + ";"
                        zpslist = pd.read_sql(com, conn)
                        wsm = zpslist.loc[0, '失败原因']
                        showinfo("原因", wsm)
                    except:
                        print("跳过")

            else:
                account.delete(0, 'end')
                password.delete(0, 'end')
                showinfo("用户登录", "账号或者密码有误！")

    def zhuce():
        def new_reguest():  # 按键操作
            n_a = new_account.get()
            n_p = new_password.get()
            n_em = new_emal.get()
            if (n_a == "" or n_p == "" or n_em == ""):
                showinfo("用户注册", "请完整填写信息！！")
            else:
                sql = "insert into 用户表  values (" + str(n_a) + "," + str(n_p) + ",\'" + str(n_em) + "\'," + str(2) + ");"
                print(sql)
                c.execute(sql)
                conn.commit()
                showinfo("用户注册", "注册成功")
                index_sign_up.destroy()
        #注册界面
        index_sign_up = tkinter.Toplevel(index)
        index_sign_up.geometry('400x300')
        index_sign_up.title('用户注册')
        canvas = tkinter.Canvas(index_sign_up, width=400, height=300, bg=None)
        image_file = tkinter.PhotoImage(file="0.gif")
        image = canvas.create_image(250, 0, anchor='n', image=image_file)
        # 标签
        new_account = tkinter.Label(index_sign_up, text='账号', bg='lightskyblue', fg='white', font=('Arial', 12),width=5,height=1)
        new_account.place(relx=0.25, rely=0.3)
        new_pasw = tkinter.Label(index_sign_up, text='密码', bg='lightskyblue', fg='white', font=('Arial', 12), width=5,height=1)
        new_pasw.place(relx=0.25, rely=0.4)
        new_emal = tkinter.Label(index_sign_up, text='邮箱', bg='lightskyblue', fg='white', font=('Arial', 12), width=5,height=1)
        new_emal.place(relx=0.25, rely=0.5)
        # 便签的输入输出框
        new_account = tkinter.Entry(index_sign_up, width=20, highlightthickness=1, highlightcolor='lightskyblue',relief='groove')  # 账号输入框
        new_account.place(relx=0.4, rely=0.3)
        new_password = tkinter.Entry(index_sign_up, show='*', highlightthickness=1, highlightcolor='lightskyblue',relief='groove')  # 密码输入框
        new_password.place(relx=0.4, rely=0.4)
        new_emal = tkinter.Entry(index_sign_up, width=20, highlightthickness=1, highlightcolor='lightskyblue',relief='groove')  # 邮箱号输入框
        new_emal.place(relx=0.4, rely=0.5)
        loginBtn = tkinter.Button(index_sign_up, text='完成', font=('宋体', 12), width=4, height=1, command=new_reguest,relief='solid',bd=0.5, bg='lightcyan')
        loginBtn.place(relx=0.41, rely=0.63)
        index_sign_up.mainloop()  # 刷新界面

    index = tkinter.Tk()
    index.title('小唐快爬 v1.0.0')  # 设置主窗口标题
    index.geometry('500x400')
    index.maxsize(500, 300)
    index.minsize(500, 300)
    # 加载图片
    canvas = tkinter.Canvas(index, width=500, height=300, bg=None)
    image_file = tkinter.PhotoImage(file="0.gif")
    image = canvas.create_image(250, 0, anchor='n', image=image_file)
    canvas.pack()
    # 账号与密码文字标签
    account_lable = tkinter.Label(index, text='账号', bg='lightskyblue', fg='white', font=('Arial', 12), width=5,height=1)
    account_lable.place(relx=0.29, rely=0.4)
    pasw_lable = tkinter.Label(index, text='密码', bg='lightskyblue', fg='white', font=('Arial', 12), width=5, height=1)
    pasw_lable.place(relx=0.29, rely=0.5)
    # 账号与密码输入框
    account = tkinter.Entry(index, width=20, highlightthickness=1, highlightcolor='lightskyblue', relief='groove')  # 账号输入框
    account.place(relx=0.4, rely=0.4)
    password = tkinter.Entry(index, show='*', highlightthickness=1, highlightcolor='lightskyblue',relief='groove')  # 密码输入框
    password.place(relx=0.4, rely=0.5)
    # 登录与注册按钮
    loginBtn = tkinter.Button(index, text='登录', font=('宋体', 12), width=4, height=1, command=denglu, relief='solid',bd=0.5, bg='lightcyan')
    loginBtn.place(relx=0.41, rely=0.63)
    loginBtn = tkinter.Button(index, text='注册', font=('宋体', 12), width=4, height=1, bd=0.5, command=zhuce,relief='solid', bg='lightcyan')
    loginBtn.place(relx=0.56, rely=0.63)
    index.mainloop()  # 刷新界面

#用户界面
def chaxun(ac):
    #菜单注销
    def call_zhuxiao():
        czz="销毁"
        yonghu(ac,czz)
        window.destroy()
        run()
    #退出
    def call_exit():
        czz = "退出"
        yonghu(ac, czz)
        window.destroy()
    #搜索网站
    def findx():
        root = Tk()
        root.geometry('400x80')
        root.title('进度条')  # 设置主窗口标题
        p_bar = Progressbar(root, length=350)
        p_bar.pack(pady=10)
        # 设置进度条最大值
        p_bar['maximum'] = 30
        p_bar['value'] = 0
        #获取
        suo_find=find.get()
        czz = "搜索网站" +str(suo_find)
        yonghu(ac, czz)
        for page in range(1, 31):
            print("正在解析" + str(page) + "页")
            p_bar['value'] = page + 1
            root.update()
            url = url_q + str(page) + url_h + str(suo_find)
            print(url)
            spider(url, headers)
        x = len(tree.get_children())
        showinfo("收集完成", "共收集到" + str(x) + "份数据")
        root.destroy()
        root.mainloop()
    #分析单个页面
    def spider(base_url, headers):
        response = requests.get(base_url, headers)  # 构建服务器
        response.encoding = response.apparent_encoding  # 获取编码格式
        response_text = response.text  # txt化页面内容
        selector = etree.HTML(response_text)  # html化
        try:
            job_list = selector.xpath("//div[@class='list__YibNq']/div")
            try:
                for job in job_list:
                    # 岗位
                    job_name = job.xpath("//div[@class='p-top__1F7CL']/a/text()[1]")
                    # print(job_name)
                    # 位置
                    job_add = job.xpath("//div[@class='p-top__1F7CL']/a/text()[2]")
                    # print(job_add)
                    # 公司名
                    job_cp = job.xpath("//div[@class='company-name__2-SjF']/a/text()")
                    # print(job_cp)
                    # 公司现状
                    job_cpinfo = job.xpath("//div[@class='industry__1HBkr']/text()")
                    # print(job_cpinfo)
                    # 钱
                    job_money = job.xpath("//div[@class='p-bom__JlNur']/span/text()")
                    # print(job_money)
                    # 学历
                    job_xl = job.xpath("//div[@class='p-bom__JlNur']/text()")
                    # print(job_xl)
                    # 描述
                    job_mess = job.xpath("//div[@class='il__3lk85']/text()")
                    # print(job_mess)
                    # 需求
                    job_ir = job.xpath("//div[@class='ir___QwEG']/span/text()")
                print("解析成功")
            except BaseException as e:
                print(e)
                print(e.__traceback__.tb_lineno)
                print("本条解析失败，跳过")
        except BaseException as e:
            print(e)
            print(e.__traceback__.tb_lineno)
            print("本页爬取失败，跳过")
        #开始写入数据
        # 插入数据
        try:
            for i in range(1, len(job_name)):
                try:
                    tree.insert("", "end", values=(job_name[i],job_add[i],job_cp[i],job_cpinfo[i],job_money[i],job_xl[i],job_mess[i],job_ir[i]))
                except:
                    print("跳过")
        except:
            print("跳过")
    #清除按钮
    def delete():
        czz = "清除"
        yonghu(ac, czz)
        x = tree.get_children()
        for item in x:
            tree.delete(item)
    #转getexcel
    def getexcel():
        czz="存储EXCEl"
        yonghu(ac,czz)
        print("调用excel")
        wb_name=find.get()
        x = tree.get_children()
        name=[]
        add=[]
        cp=[]
        cpinfo =[]
        money =[]
        xl =[]
        mess =[]
        ir=[]
        for item in x:
            try:
                item_text = tree.item(item, "values")
                #数据段
                name.append(item_text[0])
                add.append(item_text[1])
                cp.append(item_text[2])
                cpinfo .append(item_text[3])
                money .append(item_text[4])
                xl .append(item_text[5])
                mess .append(item_text[6])
                ir.append(item_text[7])
            except BaseException as e:
                print("跳过")
        saveexcel(name, add, cp, cpinfo, money, xl, mess, ir)
    #存储excel
    def saveexcel(name, add, cp, cpinfo, money, xl, mess, ir):
        try:
            wb_name=find.get()+".xls"
            r_xls = open_workbook(wb_name)  # 读取excel文件
        except BaseException as e:
            print(e)
            print(e.__traceback__.tb_lineno)
            # 没有读取到我们就创建
            file = xlwt.Workbook()
            sheet1 = file.add_sheet(u'表1', cell_overwrite_ok=True)
            sheet1.write(0, 0, "岗位")
            sheet1.write(0, 1, "位置")
            sheet1.write(0, 2, "公司名")
            sheet1.write(0, 3, "公司现状")
            sheet1.write(0, 4, "工资")
            sheet1.write(0, 5, "学历")
            sheet1.write(0, 6, "描述")
            sheet1.write(0, 7, "需求")
            file.save(wb_name)
        try:
            r_xls = open_workbook(wb_name)
            row = r_xls.sheets()[0].nrows  # 获取已有的行数
            excel = copy(r_xls)  # 将xlrd的对象转化为xlwt的对象
            worksheet = excel.get_sheet(0)  # 获取要操作的sheet
            for i in range(0, len(name)):
                worksheet.write(row + i, 0, name[i])
                worksheet.write(row + i, 1, add[i])
                worksheet.write(row + i, 2, cp[i])
                worksheet.write(row + i, 3, cpinfo[i])
                worksheet.write(row + i, 4, money[i])
                worksheet.write(row + i, 5, xl[i])
                worksheet.write(row + i, 6, mess[i])
                worksheet.write(row + i, 7, ir[i])
            excel.save(wb_name)
            print("存储成功")
        except BaseException as e:
            print(e)
            print(e.__traceback__.tb_lineno)
            print("存储失败")
    def getsql():
        print("调用sql")
        czz = "存储sql"
        yonghu(ac, czz)
        x = tree.get_children()
        name = []
        add = []
        cp = []
        cpinfo = []
        money = []
        xl = []
        mess = []
        ir = []
        for item in x:
            try:
                item_text = tree.item(item, "values")
                # 数据段
                name.append(item_text[0])
                add.append(item_text[1])
                cp.append(item_text[2])
                cpinfo.append(item_text[3])
                money.append(item_text[4])
                xl.append(item_text[5])
                mess.append(item_text[6])
                ir.append(item_text[7])
            except BaseException as e:
                print("跳过")
        savesql(name, add, cp, cpinfo, money, xl, mess, ir)
    def savesql(name, add, cp, cpinfo, money, xl, mess, ir):
        wb_name = find.get()+"_数据"
        try:
            com = "select * from " + str(wb_name) + ";"
            #看看有没有这一个表
            pd.read_sql(com, conn)
        except:
            print("没有找到表,创建表")
            try:
                com="create table "+str(wb_name) +"(岗位 varchar(225),位置 varchar(225),公司名 varchar(225),公司现状 varchar(225),工资 varchar(225),学历 varchar(225),描述 varchar(225),需求 varchar(225));"
                print(com)
                c.execute(com)
            except:
                print("创建失败")
        for i in range(1,len(name)):
            try:
                sql = "insert into "+ str(wb_name)+" values (\'"+str(name[i]) +"\',\'"+str(add[i]) + "\',\'"+str(cp[i])+ "\',\'"+str(cpinfo[i])+"\',\'"+str(money[i])+"\',\'" + str(xl[i])+"\',\'" +str(mess[i])+"\',\'"+str(ir[i])+"\');"
                print(sql)
                c.execute(sql)
                conn.commit()
            except:
                print("跳过")
        showinfo("提示", "存储完成")
    def getmail():
        czz = "发送邮箱"
        yonghu(ac, czz)
        print("发送邮箱")
        try:
            wb_name = find.get() + ".xls"
            r_xls = open_workbook(wb_name)  # 读取excel文件
        except:
            showinfo("警告", "请先保存为excel")
        com = "select 邮箱 from 用户表 where 用户账号=" + str(ac) + ";"
        try:
            eamllist = pd.read_sql(com, conn)
            emal = eamllist.loc[0, '邮箱']
            print(emal)
            receivers = emal
        except BaseException as e:
            print("没有找到账号")
        wb_name = find.get() + ".xls"
        # 邮件内容设置
        content = MIMEText("<html><h2>欢迎使用小唐快爬,在这里为你提供最最最最可爱的服务</h2>", _subtype="html", _charset="utf-8")
        msg = MIMEMultipart('related')
        msg.attach(content)
        # 添加Excel附件
        excelFile = wb_name
        excelApart = MIMEApplication(open(excelFile, 'rb').read())
        excelApart.add_header('Content-Disposition', 'attachment', filename=excelFile)
        msg.attach(excelApart)
        # 邮件标题设置
        msg['Subject'] = '叮咚！这是你刚刚爬取的数据喔！！！'
        # 发件人信息
        msg['From'] = sender
        # 收件人信息
        msg['To'] = receivers
        # 通过授权码,登录邮箱,并发送邮件
        try:
            server = smtplib.SMTP('smtp.qq.com')  # 配置QQ邮箱的smtp服务器地址
            server.login(sender, password)
            server.sendmail(msg['From'], msg['To'].split(','), msg.as_string())
            print('发送成功')
            server.quit()
        except smtplib.SMTPException as e:
            print('error', e)
    #数据分析
    def getfx():
        czz = "数据分析"
        yonghu(ac, czz)
        try:
            wb_name = find.get() + ".xls"
            r_xls = open_workbook(wb_name)  # 读取excel文件
        except:
            showinfo("警告", "请先保存为excel")
        filename=find.get()+".xls"
        df = pd.read_excel(filename)
        for i in range(df.shape[0]):
            gz = df.loc[i, '工资']
            if re.search(r'.*k-.*k', gz):
                a = eval(re.search('(.*)k-(.*)k', gz).group(1)) * 1000
                b = eval(re.search('(.*)k-(.*)k', gz).group(2)) * 1000
                num = (a + b) / 2
            try:
                df.loc[i, '工资'] = int(num) / 1000
            except:
                print("跳过")
                df.loc[i, '工资'] = 0
        visualization(df)

    def visualization(df):
        filename = find.get()
        salarys = df['工资']
        mean = round(salarys.mean(), 1)
        plt.figure(figsize=(8, 6), dpi=200)
        sns.distplot(salarys, hist=True, kde=True, kde_kws={"color": "r", "linewidth": 1.5, 'linestyle': '-'})
        plt.axvline(mean, color='r', linestyle=":")
        plt.text(mean, 0.05, '平均月薪: %.1f千' % (mean), color='k', horizontalalignment='center', fontsize=15)
        plt.xlim(0, 35)
        plt.xlabel('月薪（单位：千）')
        plt.title(filename+'行业的薪资分布')
        plt.show()

        a = df['需求']
        text = []
        n = 0
        for i in a:
            text.append(i)
            n += 1
        text = ",".join(str(i) for i in text)
        mask = cv2.imread(r'D:\data\timg.jpg')
        wordcloud = WordCloud(font_path=font, background_color="white", mask=mask).generate(text)
        plt.imshow(wordcloud, interpolation='bilinear')
        plt.axis('off')
        plt.show()


    #账号菜单
    window = Tk()
    menu1 = Menu(window, tearoff=0)
    menu1.add_command(label="注销",command=call_zhuxiao)
    menu1.add_separator()
    menu1.add_command(label="退出",command=call_exit)
    menu1.add_separator()
    mebubar = Menu(window )
    mebubar.add_cascade(label="菜单", menu=menu1)
    window.config(menu=mebubar)
    window.title('网站信息')  # 设置主窗口标题
    window.geometry('700x340')
    window.maxsize(700, 310)
    window.minsize(700, 310)
    # 搜索输入框
    find = tkinter.Entry(window, width=20, highlightthickness=1, highlightcolor='lightskyblue',relief='groove')  # 账号输入框
    find.place(relx=0.03, rely=0.05)
    #功能按键
    loginBtn = tkinter.Button(window, text='搜索', font=('宋体', 12), width=4, height=1, command=findx, relief='solid',bd=0.5, bg='skyblue')
    loginBtn.place(relx=0.26, rely=0.05)
    loginBtn = tkinter.Button(window, text='发送邮箱', font=('宋体', 12), width=8, height=1, command=getmail, relief='solid', bd=0.5,bg='skyblue')
    loginBtn.place(relx=0.35, rely=0.05)
    loginBtn = tkinter.Button(window, text='转存Excel', font=('宋体', 12), width=10, height=1, command=getexcel, relief='solid', bd=0.5,bg='skyblue')
    loginBtn.place(relx=0.48, rely=0.05)
    loginBtn = tkinter.Button(window, text='转存SQL', font=('宋体', 12), width=9, height=1, command=getsql, relief='solid',bd=0.5,bg='skyblue')
    loginBtn.place(relx=0.63, rely=0.05)
    loginBtn = tkinter.Button(window, text='一键分析', font=('宋体', 12), width=8, height=1, command=getfx, relief='solid',bd=0.5,bg='skyblue')
    loginBtn.place(relx=0.77, rely=0.05)
    loginBtn = tkinter.Button(window, text='清除', font=('宋体', 12), width=4, height=1, command=delete, relief='solid',bd=0.5, bg='skyblue')
    loginBtn.place(relx=0.9, rely=0.05)

    cols = ("岗位", "位置","公司名","公司现状","工资","学历","描述","需求")
    ybar = Scrollbar(window)
    tree = Treeview(window, show='headings', columns=cols, yscrollcommand=ybar.set)
    ybar['command'] = tree.yview
    # 表头设置
    for col in cols:
        tree.heading(col, text=col)  # 行标题
        tree.column(col, width=80, anchor='w')  # 每一行的宽度,'w'意思为靠右
    #*********************************************************************************************************************测试
    for i in range(1, 500):
        tree.insert("", "end", values=("john", "1" + str(i),"1","2","1","2","1","2"))

    gs= tkinter.Label(window, text=' ', fg='white', font=('Arial', 12), width=2, height=3)
    gs.grid(row=0, column=0)
    tree.grid(row=1, column=1)  # grid方案
    ybar.grid(row=1, column=2, sticky='ns')
    window.mainloop()





#*****************************************管理员界面*******************************************
#管理员管理管理员
def guanldl(ac):
    # 菜单注销
    def call_zhuxiao():
        window.destroy()
        run()
    # 退出
    def call_exit():
        window.destroy()
    # 切换成管理员管理管理员
    def qiehuan():
        window.destroy()
        guanli(ac)
    # 清除按钮
    def delete():
        x = tree.get_children()
        for item in x:
             tree.delete(item)
    #查找
    def find1():
        delete()
        findy = find.get()
        czz = "查询" +findy
        gly(ac, czz)
        try:
            com = "select * from 管理员操作表 where 管理员账号=" + str(findy) + ";"
            zpslist = pd.read_sql(com, conn)
            for i in range(zpslist.shape[0]):
                zh = zpslist.loc[i, '管理员账号']
                cz = zpslist.loc[i, '管理员操作']
                tree.insert("", "end", values=(zh, cz))
            showinfo("提示", "查找成功")
        except:
            showinfo("提示", "查找失败")
            print("刷新失败")

    # 删除按键
    def drop():
        acc = a[-1]
        czz = "删除" + acc
        gly(ac, czz)
        print(acc)
        com = "delete from 管理员操作表 where 管理员账号=" + "\'" + str(acc) + "\'" + ";"
        print(com)
        try:
            c.execute(com)
            conn.commit()
            showinfo("提示", "删除成功")
        except:
            showinfo("提示", "删除失败")
    #新增管理员
    def add():
        def new_reguest():  # 按键操作
            n_a = new_account.get()
            n_p = new_password.get()
            czz="新增管理员"+n_a
            gly(ac,czz)
            if (n_a == "" or n_p == ""):
                showinfo("用户注册", "请完整填写信息！！")
            else:
                sql = "insert into 管理员表  values (" + str(n_a) + "," + str(n_p) + ");"
                print(sql)
                c.execute(sql)
                conn.commit()
                showinfo("用户注册", "注册成功")
                zhuce.destroy()
        zhuce = Tk()
        zhuce.title('新增管理员')  # 设置主窗口标题
        zhuce.geometry('400x300')
        zhuce.maxsize(400, 300)
        zhuce.minsize(400, 300)
        # 标签
        new_account = tkinter.Label(zhuce, text='账号', bg='lightskyblue', fg='white', font=('Arial', 12),width=5, height=1)
        new_account.place(relx=0.25, rely=0.3)
        new_pasw = tkinter.Label(zhuce, text='密码', bg='lightskyblue', fg='white', font=('Arial', 12), width=5,height=1)
        new_pasw.place(relx=0.25, rely=0.4)
        # 便签的输入输出框
        new_account = tkinter.Entry(zhuce, width=20, highlightthickness=1, highlightcolor='lightskyblue',relief='groove')  # 账号输入框
        new_account.place(relx=0.4, rely=0.3)
        new_password = tkinter.Entry(zhuce, show='*', highlightthickness=1, highlightcolor='lightskyblue',relief='groove')  # 密码输入框
        new_password.place(relx=0.4, rely=0.4)
        loginBtn = tkinter.Button(zhuce, text='完成', font=('宋体', 12), width=4, height=1, command=new_reguest,relief='solid', bd=0.5, bg='lightcyan')
        loginBtn.place(relx=0.41, rely=0.63)
        zhuce.mainloop()  # 刷新界面




    # 账号菜单
    a = []
    window = Tk()
    menu1 = Menu(window, tearoff=0)
    menu1.add_command(label="切换管理员", command=qiehuan)
    menu1.add_separator()
    menu1.add_command(label="注销", command=call_zhuxiao)
    menu1.add_separator()
    menu1.add_command(label="退出", command=call_exit)
    menu1.add_separator()
    mebubar = Menu(window)
    mebubar.add_cascade(label="菜单", menu=menu1)
    window.config(menu=mebubar)
    window.title('管理管理用户')  # 设置主窗口标题
    window.geometry('450x340')
    window.maxsize(450, 310)
    window.minsize(450, 310)

    # 搜索输入框
    find = tkinter.Entry(window, width=20, highlightthickness=1, highlightcolor='lightskyblue',relief='groove')  # 账号输入框
    find.place(relx=0.06, rely=0.05)
    # 功能按键
    cols = ("账号", "操作")
    ybar = Scrollbar(window)
    tree = Treeview(window, show='headings', columns=cols, yscrollcommand=ybar.set)
    ybar['command'] = tree.yview
    # 表头设置
    for col in cols:
        tree.heading(col, text=col)  # 行标题
        tree.column(col, width=200, anchor='w')  # 每一行的宽度,'w'意思为靠右
    gs = tkinter.Label(window, text=' ', fg='white', font=('Arial', 12), width=2, height=3)
    gs.grid(row=0, column=0)
    tree.grid(row=1, column=1)  # grid方案
    ybar.grid(row=1, column=2, sticky='ns')

    loginBtn = tkinter.Button(window, text='搜索', font=('宋体', 12), width=4, height=1, command=find1, relief='solid',bd=0.5, bg='skyblue')
    loginBtn.place(relx=0.42, rely=0.05)
    loginBtn = tkinter.Button(window, text='清空', font=('宋体', 12), width=4, height=1, command=delete, relief='solid',bd=0.5, bg='skyblue')
    loginBtn.place(relx=0.55, rely=0.05)
    loginBtn = tkinter.Button(window, text='删除', font=('宋体', 12), width=4, height=1, command=drop, relief='solid',bd=0.5, bg='skyblue')
    loginBtn.place(relx=0.67, rely=0.05)
    loginBtn = tkinter.Button(window, text='添加管理员', font=('宋体', 12), width=9, height=1, command=add, relief='solid',bd=0.5, bg='skyblue')
    loginBtn.place(relx=0.8, rely=0.05)

    def treeviewClick(event):  # 单击
        for item in tree.selection():
            item_text = tree.item(item, "values")
            print(item_text[0])
            a.append(item_text[0])

    tree.bind('<ButtonRelease-1>', treeviewClick)
    window.mainloop()

#管理员管理普通用户
def guanli(ac):
    #菜单注销
    def call_zhuxiao():
        window.destroy()
        run()
    #退出
    def call_exit():
        window.destroy()
    #切换成管理员管理管理员
    def qiehuan():
        window.destroy()
        guanldl(ac)
    # 清除按钮
    def delete():
        x = tree.get_children()
        for item in x:
            tree.delete(item)
    #删除按键
    def drop():
        acc=a[-1]
        print(acc)
        czz = "删除普通用户" + acc
        gly(ac, czz)
        com="delete from 用户表 where 用户账号=" + "\'"+ str(acc)+ "\'"+";"
        print(com)
        try:
            c.execute(com)
            conn.commit()
            showinfo("提示", "删除成功")
        except:
            showinfo("提示", "删除失败")
    #刷新按键
    def shauxin():
        x = tree.get_children()
        for item in x:
            tree.delete(item)
        try:
            com = "select * from 用户表 " + ";"
            zpslist = pd.read_sql(com, conn)
            for i in range(zpslist.shape[0]):
                zh = zpslist.loc[i, '用户账号']
                mm = zpslist.loc[i, '用户密码']
                email = zpslist.loc[i, '邮箱']
                temp = zpslist.loc[i, '判定值']
                if temp == '1':
                    zt = "正常"
                elif temp == '2':
                    zt = "审核中"
                elif temp == '3':
                    zt = "审核失败"
                tree.insert("", "end", values=(zh, mm, email, zt))
            showinfo("提示", "刷新成功")
        except:
            showinfo("提示", "刷新失败")
            print("刷新失败")
    #搜索按钮
    def finx():
        x = tree.get_children()
        for item in x:
            tree.delete(item)
        what=find.get()
        print(what)
        try:
            com = "select * from 用户表 where 用户账号=" + str(what) + ";"
            zpslist = pd.read_sql(com, conn)
            print(zpslist)
            zh = zpslist.loc[0, '用户账号']
            mm = zpslist.loc[0, '用户密码']
            email = zpslist.loc[0, '邮箱']
            temp = zpslist.loc[0, '判定值']
            if temp == '1':
                zt = "正常"
            elif temp == '2':
                zt = "审核中"
            elif temp == '3':
                zt = "审核失败"
            tree.insert("", "end", values=(zh, mm, email, zt))
            showinfo("提示", "查询成功")
        except:
            showinfo("提示", "没有找到该用户")
    # 审查按钮
    def shenhe():
        #刷新
        def ctrlE():
            x = tree2.get_children()
            for item in x:
                tree2.delete(item)
            try:
                com = "select * from 用户表 where 判定值='2'" + ";"
                zpslist = pd.read_sql(com, conn)
                for i in range(zpslist.shape[0]):
                    zh = zpslist.loc[i, '用户账号']
                    mm = zpslist.loc[i, '用户密码']
                    email = zpslist.loc[i, '邮箱']
                    temp = zpslist.loc[i, '判定值']
                    if temp == '1':
                        zt = "正常"
                    elif temp == '2':
                        zt = "审核中"
                    elif temp == '3':
                        zt = "审核失败"
                    tree2.insert("", "end", values=(zh, mm, email, zt))
                showinfo("提示", "刷新成功")
            except:
                showinfo("提示", "刷新失败")
                print("刷新失败")
        #通过
        def Tpass():
            print(a)
            acc = a[-1]
            print(acc)
            com = "update 用户表 set 判定值='1' where 用户账号=" + "\'" + str(acc) + "\'" + ";"
            print(com)
            try:
                c.execute(com)
                conn.commit()
                showinfo("提示", "通过成功")
            except:
                showinfo("提示", "通过失败")
        #不通过
        def nopass():
            acc = a[-1]
            print(acc)
            com = "update 用户表 set 判定值='3' where 用户账号=" + "\'" + str(acc) + "\'" + ";"
            print(com)
            try:
                c.execute(com)
                conn.commit()
                result = tkinter.simpledialog.askstring(title = '原因',prompt='请输入不通过原因')
                sql = "insert into 注册失败表" +" values (\'" + str(acc) + "\',\'" + str(ac) + "\',\'"+result+"\');"
                print(sql)
                c.execute(sql)
                conn.commit()
                showinfo("提示", "操作成功")
            except:
                showinfo("提示", "操作失败")


        a=[]
        shenhe = Tk()
        shenhe.title('资格审核')  # 设置主窗口标题
        shenhe.geometry('400x300')
        shenhe.maxsize(400,300)
        shenhe.minsize(400, 300)
        cols = ("账号", "密码", "邮箱", "状态")
        ybar = Scrollbar(shenhe)
        tree2 = Treeview(shenhe, show='headings', columns=cols, yscrollcommand=ybar.set)
        ybar['command'] = tree2.yview
        # 表头设置
        for col in cols:
            tree2.heading(col, text=col)  # 行标题
            tree2.column(col, width=100, anchor='w')  # 每一行的宽度,'w'意思为靠右
        tree2.grid(row=1, column=1)  # grid方案
        ybar.grid(row=1, column=2, sticky='ns')
        loginBtn = tkinter.Button(shenhe, text='通过', font=('宋体', 16), width=5, height=1, command=Tpass, relief='solid',bd=0.5, bg='skyblue')
        loginBtn.place(relx=0.15, rely=0.83)
        loginBtn = tkinter.Button(shenhe, text='刷新', font=('宋体', 16), width=5, height=1, command=ctrlE, relief='solid',bd=0.5, bg='skyblue')
        loginBtn.place(relx=0.45, rely=0.83)
        loginBtn = tkinter.Button(shenhe, text='不通过', font=('宋体', 16), width=5, height=1, command=nopass, relief='solid',bd=0.5, bg='skyblue')
        loginBtn.place(relx=0.75, rely=0.83)

        def treeviewClick(event):  # 单击
            for item in tree2.selection():
                item_text = tree2.item(item, "values")
                print(item_text[0])
                a.append(item_text[0])
        tree2.bind('<ButtonRelease-1>', treeviewClick)
        shenhe.mainloop()
    #记录查询
    def hostfind():
        def finyh():
            x = tree3.get_children()
            for item in x:
                tree3.delete(item)
            findy=find.get()
            try:
                com = "select * from 用户操作表 where 用户账号=" + str(findy) + ";"
                zpslist = pd.read_sql(com, conn)
                for i in range(zpslist.shape[0]):
                    zh = zpslist.loc[i, '用户账号']
                    cz = zpslist.loc[i, '用户操作']
                    tree3.insert("", "end", values=(zh, cz))
                showinfo("提示", "查找成功")
            except:
                showinfo("提示", "查找失败")
                print("刷新失败")
        shenhe = Tk()
        shenhe.title('记录审核')  # 设置主窗口标题
        shenhe.geometry('280x300')
        shenhe.maxsize(260, 300)
        shenhe.minsize(260, 300)
        cols = ("用户账号", "用户操作")
        ybar = Scrollbar(shenhe)
        tree3 = Treeview(shenhe, show='headings', columns=cols, yscrollcommand=ybar.set)
        ybar['command'] = tree3.yview
        # 表头设置
        for col in cols:
            tree3.heading(col, text=col)  # 行标题
            tree3.column(col, width=100, anchor='w')  # 每一行的宽度,'w'意思为靠右
        tree3.grid(row=1, column=1)  # grid方案
        ybar.grid(row=1, column=2, sticky='ns')
        gs = tkinter.Label(shenhe, text=' ', fg='white', font=('Arial', 12), width=2, height=3)
        gs.grid(row=0, column=0)
        loginBtn = tkinter.Button(shenhe, text='搜索', font=('宋体', 12), width=4, height=1, command=finyh, relief='solid',bd=0.5, bg='skyblue')
        loginBtn.place(relx=0.55, rely=0.05)
        # 输入框
        find = tkinter.Entry(shenhe, width=13, highlightthickness=1, highlightcolor='lightskyblue',relief='groove')
        find.place(relx=0.08, rely=0.05)
        shenhe.mainloop()

    a=[]
    window = Tk()
    menu1 = Menu(window, tearoff=0)
    menu1.add_command(label="切换管理员", command=qiehuan)
    menu1.add_separator()
    menu1.add_command(label="注销", command=call_zhuxiao)
    menu1.add_separator()
    menu1.add_command(label="退出", command=call_exit)
    menu1.add_separator()
    mebubar = Menu(window)
    mebubar.add_cascade(label="菜单", menu=menu1)
    window.config(menu=mebubar)
    window.title('管理普通用户')  # 设置主窗口标题
    window.geometry('700x340')
    window.maxsize(700, 310)
    window.minsize(700, 310)

    # 搜索输入框
    find = tkinter.Entry(window, width=20, highlightthickness=1, highlightcolor='lightskyblue',relief='groove')  # 账号输入框
    find.place(relx=0.03, rely=0.05)
    # 功能按键
    cols = ("账号", "密码", "邮箱", "状态")
    ybar = Scrollbar(window)
    tree = Treeview(window, show='headings', columns=cols, yscrollcommand=ybar.set)
    ybar['command'] = tree.yview
    # 表头设置
    for col in cols:
        tree.heading(col, text=col)  # 行标题
        tree.column(col, width=100, anchor='w')  # 每一行的宽度,'w'意思为靠右
    gs = tkinter.Label(window, text=' ', fg='white', font=('Arial', 12), width=2, height=3)
    gs.grid(row=0, column=0)
    tree.grid(row=1, column=1)  # grid方案
    ybar.grid(row=1, column=2, sticky='ns')

    loginBtn = tkinter.Button(window, text='搜索', font=('宋体', 12), width=4, height=1, command=finx, relief='solid',bd=0.5, bg='skyblue')
    loginBtn.place(relx=0.26, rely=0.05)
    loginBtn = tkinter.Button(window, text='清空', font=('宋体', 12), width=4, height=1, command=delete, relief='solid',bd=0.5, bg='skyblue')
    loginBtn.place(relx=0.36, rely=0.05)
    loginBtn = tkinter.Button(window, text='删除', font=('宋体', 12), width=4, height=1, command=drop, relief='solid',bd=0.5, bg='skyblue')
    loginBtn.place(relx=0.45, rely=0.05)
    loginBtn = tkinter.Button(window, text='刷新', font=('宋体', 12), width=4, height=1, command=shauxin, relief='solid',bd=0.5, bg='skyblue')
    loginBtn.place(relx=0.54, rely=0.05)

    loginBtn = tkinter.Button(window, text='资格审核', font=('宋体', 12), width=12, height=2, command=shenhe,relief='solid',bd=0.5, bg='skyblue')
    loginBtn.place(relx=0.78, rely=0.3)
    loginBtn = tkinter.Button(window, text='用户历史查询', font=('宋体', 12), width=12, height=2, command=hostfind, relief='solid',bd=0.5, bg='skyblue')
    loginBtn.place(relx=0.78, rely=0.5)

    def treeviewClick(event):  # 单击
        for item in tree.selection():
            item_text = tree.item(item, "values")
            print(item_text[0])
            a.append(item_text[0])
    tree.bind('<ButtonRelease-1>', treeviewClick)
    window.mainloop()

run()